# -*- coding: utf8 -*-
from ..mysqlconn import conn
import json
import logging
qa_logger = logging.getLogger('qa_logger')

# 查询质检任务的数据(机器人) #
def get_taskid_quality_robot_data(task_id, state, sample_id=None):
    sql = """
    SELECT
        CONCAT(d.voxfile,'.mp3') as voxfile,
        s.sampleid,
        d.saleid,
        s.voice_taskid,
        s.voice_result,
        0 as cause
    from zt_gc_ai_sample s,zt_sale_data d
    where s.taskid= '{}' AND s.state='{}' and d.sale id = s.saleid and s.service_type=7
    """.format(task_id, state)
    if sample_id is not None:
        sql += " and s.sampleid='%s'" % sample_id
    data = conn.mysql_seldic(sql, conn.basedbname, task_id)
    return data


def get_task_quality_service_data(task_id, sampleid=None, state=None):
    """
    得到检测服务数据，仅适用于 quality_test 接口
    :param taskid:
    :param sampleid:
    :param state:
    :return:
    """
    sql = """
        SELECT
            CONCAT(c.voxfile,'.mp3') as voxfile,
            c.cause,
            s.sampleid,
            s.voice_taskid,
            s.voice_result,
            s.scid
        from
            zt_gc_ai_sample s,
            zt_service_cust c
        where s.scid=c.ServiceID and s.taskid={} and s.service_type=5
    """.format(task_id)

    if state is not None:
        sql += " and s.state = '%s' " % state
    if sampleid is not None:
        sql += " and s.sampleid= '%s' " % sampleid
    data = conn.mysql_seldic(sql, conn.basedbname, task_id)
    return data


# 查询质检任务的数据(机器人)
def get_task_single_quality_service_data(task_id, sample_id=None, state=None):
    sql = """
    SELECT CONCAT(c.voxfile,'.mp3') as voxfile,c.cause,s.sampleid,s.voice_taskid,s.voice_result,s.scid
    from zt_gc_ai_sample s,zt_service_cust c
    where s.scid=c.ServiceID and s.taskid={} and s.service_type=5
    """.format(task_id)
    if state is not None:
        if str(state) == "0":
            sql += " and s.state >= 0 and s.state < 6 "  # 只取正常数据
        else:
            sql += " and s.state = %s " % state
    if sample_id is not None:
        sql += " and s.sampleid= %s " % sample_id
    data = conn.mysql_seldic(sql, conn.basedbname, task_id)
    return data


# def update_quality_task_id(,):

# 更新质检任务的录音翻译taskid
def updata_quality_voice_taskid(voice_taskid, taskid, sampleid, voice_result, state):
    sql = "UPDATE zt_gc_ai_sample set state='%s'" % state

    if voice_taskid != "":
        sql += " ,voice_taskid='" + str(voice_taskid) + "' "
    elif voice_result != "":
        sql += ",voice_result=" + json.dumps(voice_result, ensure_ascii=False) + " "
    sql += " where sampleid = '" + str(sampleid) + "'"
    # print(sql)
    data = conn.mysql_updata(sql, conn.basedbname, taskid)


# 查询那些数据还没返回翻译结果
def sql_voice_result(taskid):
    sql = "SELECT d.voxfile,s.sampleid,d.saleid,s.voice_taskid from zt_gc_ai_sample s,zt_sale_data d " \
          "where  s.state in (1,2) and d.saleid=s.saleid and s.taskid='" + str(taskid) + "'  "
    data = conn.mysql_seldic(sql, conn.basedbname, taskid)
    # print(data)
    return data


# 查询质检规则
def sql_quality_rules(taskid):
    sql = "SELECT l.ruleid,l.weight,c.ctypeid,c.role,c.checkrange,c.min_range,c.max_range," \
          "c.content,c.min_time,c.max_range,c.close_time,c.over_num,c.fast_num,c.fast_ignore,c.mute_time,r.score,r.cmpid,c.conditionid " \
          "from zt_gc_ai_plan_rule_link l,zt_gc_ai_condition c,zt_gc_ai_rule r,zt_gc_ai_task t where c.state=1 and  t.taskid='" + str(
        taskid) + "' and l.planid = t.planid" \
                  " and l.ruleid= r.ruleid and r.ruleid=c.ruleid and r.state=1 and l.state=1 and c.state=1"
    data = conn.mysql_seldic(sql, conn.basedbname, taskid)
    return data


# 查询任务对应的质检规则
def sel_taskid_rules(taskid):
    sql = "select l.ruleid,l.weight,r.logic,r.score,r.is_send,r.`name` ,r.createtime,0 as is_hangoff,is_potential  from zt_gc_ai_task_rule_link l ,zt_gc_ai_rule r " \
          " where r.ruleid=l.ruleid and l.taskid=" + str(taskid) + " and r.state=1"
    # sql = "SELECT p.ruleid,p.weight,r.logic,r.score,r.is_send,r.`name`,r.createtime,r.is_hangoff from zt_gc_ai_plan_rule_link p ,zt_gc_ai_task t,zt_gc_ai_rule r " \
    #       "where  p.planid=t.planid and p.ruleid=r.ruleid and p.state=1 and r.state=1 and t.taskid='"+str(taskid)+"'"
    data = conn.mysql_seldic(sql, conn.basedbname, taskid)
    return data


# 查询规则编码对应的规则
def sql_rules_for_data(ruleid, taskid):
    sql = """
    SELECT
        c.ctypeid,c.role,c.checkrange  ,c.min_range,c.max_time,c.max_range,c.content,c.min_time,c.close_time,
        c.over_num,c.over_time,c.fast_num,c.fast_ignore,c.mute_time,c.conditionid,c.is_match,c.sort,c.word_num,c.word_range
    from zt_gc_ai_condition c where c.ruleid='{}'
    """.format(ruleid)

    data = conn.mysql_seldic(sql, conn.basedbname, taskid)
    return data


# 查询公司的分数等级区分值
def sel_score_data(cmp_id, type, task_id):
    """

    :param cmp_id: 公司编码
    :param type:  1结果标签 2语速统计 3静音时长统计 4录音时长统计
    :param task_id: 任务编码
    :return:
    """
    sql = """
    SELECT
        t.verygood_max,t.verygood_min,t.good_max,t.good_min,t.average_max,
        t.average_min,t.poor_max,t.poor_min,t.verypoor_max,t.verypoor_min
    from zt_gc_ai_setting t where t.cmpid='{}' and t.type='{}'
    """.format(cmp_id, type)
    data = conn.mysql_seldic(sql, conn.basedbname, task_id)
    return data


# 存储单项规则得分
def insert_rule_score(ruleid, score, sampleid, is_match, taskid):
    sql = "INSERT into zt_gc_ai_sample_score(ruleid,score,sampleid,is_match) VALUES " \
          " ('" + str(ruleid) + "','" + str(score) + "','" + str(sampleid) + "','" + str(is_match) + "')"
    print('444444444444444444', sql)
    data = conn.mysql_insertid(sql, conn.basedbname, taskid)


def update_single_quality_result(total, total_lable, mute_score, mute_lable, fast_lable, fast_score, calltime_lable,
                                 calltime_score, sample_id, task_id, state, is_hangoff, g_is_potential, voice_result,
                                 voice_taskid):
    sql = """
                UPDATE zt_gc_ai_sample set
                    total='{}', total_lable='{}', mute_score='{}', mute_lable='{}', fast_lable='{}', fast_score='{}',
                    calltime_lable='{}', calltime_score='{}', state='{}', is_hangoff='{}', is_potential={}, voice_result={},
                    voice_taskid='{}'
                where sampleid={sample_id}
                """
    sql = sql.format(total, total_lable, mute_score, mute_lable, fast_lable, fast_score, calltime_lable, calltime_score,
                     state, is_hangoff, g_is_potential, json.dumps(voice_result, ensure_ascii=False), voice_taskid,
                     sample_id=sample_id)
    sql = sql.replace('None', 'NULL')
    data = conn.mysql_updata(sql, conn.basedbname, task_id)


# 更新会话质检结果
def update_quality_result(total, total_lable, mute_score, mute_lable, fast_lable, fast_score, calltime_lable,
                          calltime_score, sample_id, task_id, state, is_hangoff):
    sql = """
            UPDATE zt_gc_ai_sample set
                total='{}', total_lable='{}', mute_score='{}', mute_lable='{}', fast_lable='{}', fast_score='{}',
                calltime_lable='{}', calltime_score='{}', state='{}', is_hangoff='{}'
            where sampleid={sample_id}
            """
    sql = sql.format(total, total_lable, mute_score, mute_lable, fast_lable, fast_score, calltime_lable, calltime_score,
                     state, is_hangoff, sample_id=sample_id)
    data = conn.mysql_updata(sql, conn.basedbname, task_id)


# 查询是否还有数据没质检
def sel_count_quality_data(taskid):
    sql = "SELECT count(*) from zt_gc_ai_sample s where s.state  in (0,1,2,3) and s.taskid='" + str(
        taskid) + "' and  service_type is not null"
    data = conn.mysql_selrowid(sql, conn.basedbname, taskid)
    return data


# 更新质检状态
def updata_quality_state(taskid):
    sql = "UPDATE zt_gc_ai_task set state=3 ,end_time=NOW() WHERE taskid='" + str(taskid) + "' "
    conn.mysql_updata(sql, conn.basedbname, taskid)


# 查询正在质检的任务
def sel_quality_tasking():
    sql = "SELECT t.taskid,t.cmpid,t.service_type from zt_gc_ai_task t where t.state=1"
    data = conn.mysql_seldic(sql, conn.basedbname, '')
    return data


# 查询计费
def billing_balance(taskid, cmpid):
    sql = "SELECT c.checkfee,c.balance from zt_company c where c.COMPANYID = '" + str(cmpid) + "'"
    data = conn.mysql_seldic(sql, conn.basedbname, taskid)
    return data


# 插入质检单条计费
def insert_bulling(cmpid, duration, price, sampleid, taskid):
    sql = "INSERT into zt_pay_bill(cmpid,calltime,duration,price,ptype,sampleid) VALUES " \
          "('" + str(cmpid) + "',NOW(),'" + str(duration) + "','" + str(price) + "',3,'" + str(sampleid) + "')"
    data = conn.mysql_insertid(sql, conn.basedbname, taskid)
    return data


# 更新余额
def updata_balance(cmpid, taskid, balance):
    sql = "UPDATE zt_company set balance='" + str(balance) + "' where COMPANYID='" + str(cmpid) + "';"
    data = conn.mysql_updata(sql, conn.basedbname, taskid)
    return data


# 删除已质检的数据
def del_quality_data(sampleid, taskid):
    sql = "DELETE from zt_gc_ai_sample_score where sampleid='" + str(sampleid) + "';"
    data = conn.mysql_updata(sql, conn.basedbname, taskid)
    return data


# 记录质检规则
def insert_task_rule_link(ruleid, weight, taskid, sampleid):
    sql = "insert into zt_gc_ai_task_rule_link(ruleid,weight,taskid,sampleid) " \
          "VALUES ('" + str(ruleid) + "','" + str(weight) + "','" + str(taskid) + "','" + str(sampleid) + "')"
    data = conn.mysql_insertid(sql, conn.basedbname, taskid)
    return data


def insert_condition_use(taskid, conditionid, ruleid, ctypeid, role, checkrange, min_range, max_range, content,
                         is_match, min_time, max_time, close_time, over_num, over_time, fast_num, fast_ignore,
                         mute_time, sort, state, sampleid):
    sql = "insert into zt_gc_ai_condition_use (conditionid,ruleid,ctypeid,role,checkrange,min_range,max_range,content,is_match,min_time,max_time," \
          "close_time,over_num,over_time,fast_num,fast_ignore,mute_time,sort,state,sampleid) VALUES (" + str(
        conditionid) + "," + str(ruleid) + "," + str(ctypeid) + "," \
                                                                "" + str(role) + "," + str(checkrange) + "," + str(
        min_range) + "," + str(max_range) + ",'" + str(content) + "'," + str(is_match) + "," + str(
        min_time) + "," + str(max_time) + "," + str(close_time) + "" \
                                                                  "," + str(over_num) + "," + str(
        over_time) + "," + str(fast_num) + "," + str(fast_ignore) + "," + str(mute_time) + "," + str(sort) + "," + str(
        state) + "," + str(sampleid) + ")"
    print(sql)
    sql = sql.replace('None', 'NULL')
    data = conn.mysql_insertid(sql, conn.basedbname, taskid)
    return data


def insert_rule_use(taskid, ruleid, name, is_send, logic, cmpid, state, score, createtime, sampleid):
    sql = "insert into zt_gc_ai_rule_use(ruleid,name,is_send,logic,cmpid,state,score,createtime,sampleid) VALUES" \
          " (" + str(ruleid) + "," + str(name) + "," + str(is_send) + "," + str(logic) + "," + str(cmpid) + "," + str(
        state) + "," + str(score) + ",'" + str(createtime) + "'," + str(sampleid) + ")"
    sql = sql.replace('None', 'NULL')
    data = conn.mysql_insertid(sql, conn.basedbname, taskid)
    return data


# 插入word
def insert_word(word, taskid, sampleid, qtype):
    sql = "insert into zt_gc_ai_word(word,sampleid,qtype) VALUES ('" + str(word) + "','" + str(sampleid) + "','" + str(
        qtype) + "')"
    conn.mysql_insertid(sql, conn.basedbname, taskid)


# 插入匹配到的问题库
def insert_question_id(questionid, sampleid, taskid):
    sql = "insert into zt_gc_ai_quality_question(questionid,sampleid) VALUES ('" + str(questionid) + "','" + str(
        sampleid) + "')"
    conn.mysql_insertid(sql, conn.basedbname, taskid)


# 插入质检记录
def insert_service_cust(cmpid, isinside, Begintime, voxflie, taskid, userid, call_numb, called_numb):
    # cause 0客户断开 1坐席断开
    # isinside1内部质询，2呼入，3呼出
    sql = "insert into zt_service_cust(cmpid,state,isinside,Begintime,voxfile,agentid,calloutnum,callernum,Servicetype) VALUES " \
          "('" + str(cmpid) + "',2,'" + str(isinside) + "','" + str(Begintime) + "','" + str(voxflie) + "','" + str(
        userid) + "','" + str(call_numb) + "','" + str(called_numb) + "',5)"
    id = conn.mysql_insertid(sql, conn.basedbname, taskid)
    return id


# 插入质检数据
def insert_gc_ai_sample(scid, taskid):
    sql = "insert into zt_gc_ai_sample(scid,service_type) VALUES('" + str(scid) + "',5)"
    id = conn.mysql_insertid(sql, conn.basedbname, taskid)
    return id


# 插入在线质检聊天记录
def insert_record_data(typeid, text, voice, sampleid, taskid):
    sql = "insert into zt_gc_ai_call_record(typeid,text,voice,sampleid) VALUES" \
          "('" + str(typeid) + "','" + str(text) + "','" + str(voice) + "','" + str(sampleid) + "')"
    print(sql)
    conn.mysql_insertid(sql, conn.basedbname, taskid)


# 查找客服编码
def get_useid(phone, taskid):
    sql = "SELECT r.UserID,u.companyid from zt_guser r,zt_cmp_users u where r.Phone='" + str(
        phone) + "' and u.guserid=r.UserID and u.`status`=1"
    data = conn.mysql_seldic(sql, conn.basedbname, taskid)
    return data


def get_tasid(phone, taskid, cmpid):
    sql = "SELECT  t.taskid,t.cmpid  from zt_line_info i ,zt_gc_ai_task t where i.lineID=t.lineid and i.virtual_phone='" + str(
        phone) + "' and t.cmpid='" + str(cmpid) + "'"
    data = conn.mysql_seldic(sql, conn.basedbname, taskid)
    return data


# 更新服务表数据
def update_service_cust(serviceid, taskid, Endtime):
    sql = "UPDATE zt_service_cust set state=3 ,Endtime='" + str(Endtime) + "' where ServiceID='" + str(serviceid) + "'"
    conn.mysql_updata(sql, conn.basedbname, taskid)


# 查询质检的时间段
def sel_quality_time(cmpid, taskid):
    sql = "SELECT s.time_begin,s.time_end from zt_gc_ai_setting s WHERE s.cmpid='" + str(cmpid) + "' and s.type=5"
    data = conn.mysql_seldic(sql, conn.basedbname, taskid)
    return data


# 查询规则是否已经命中过
def sel_if_is_math(sampleid, ruleid, taskid):
    sql = "SELECT s.is_match from zt_gc_ai_sample_score s where s.sampleid='" + str(sampleid) + "' and ruleid='" + str(
        ruleid) + "'"
    data = conn.mysql_selrowid(sql, conn.basedbname, taskid)
    return data


# 在线质检时更新触发状态
def update_is_math(score, ruleid, sampleid, taskid):
    sql = "UPDATE zt_gc_ai_sample_score set score='" + str(score) + "' ,is_match=1 where ruleid='" + str(
        ruleid) + "' and sampleid='" + str(sampleid) + "'"
    data = conn.mysql_updata(sql, conn.basedbname, taskid)


# 查询公司配置的情绪值
def sel_point_data(cmpid, sampleid, taskid):
    sql = "SELECT s.high_point,s.low_point from zt_gc_ai_setting s where s.cmpid='" + str(cmpid) + "' and s.type=6"
    data = conn.mysql_seldic(sql, conn.basedbname, taskid)
    return data
